﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using _4T.Data;
using _4T.Data.IDataContext;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using CMS.Core.Libs;

namespace _4T.DAL
{
    public class ConsiderAndDecideDAL
    {

        public ConsiderAndDecideDAL()
        {

        }

        ~ConsiderAndDecideDAL()
        {

        }

        public int DeleteCDWhenApprove(Guid considerAndDecideID)
        {
            var conn = new SqlConnection(Ultility.connectionString);
            conn.Open();
            var cmd = new SqlCommand();
            cmd.CommandText = "sp_Approved";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ConsiderAndDecideID", SqlDbType.UniqueIdentifier).Value = considerAndDecideID;
            cmd.Connection = conn;
            return cmd.ExecuteNonQuery();
        }

        public IEnumerable<ProfileFilterCus> GetInfoFilterByProfileCode(string profileCode)
        {
            var conn = new SqlConnection(Ultility.connectionString);
            conn.Open();
            var cmd = new SqlCommand();
            cmd.CommandText = "ProfileGetFilterInfo";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ProfileCode", SqlDbType.NVarChar).Value = profileCode;
            cmd.Connection = conn;
            var reader = cmd.ExecuteReader();

            if (reader != null && reader.HasRows)
            {
                return reader.DatareaderToList(n => new ProfileFilterCus
                {
                    Filter = n["Filter"] is DBNull ? string.Empty : n["Filter"].ToString(),
                    FilterTime = n["FilterTime"] is DBNull ? DateTime.MinValue : Convert.ToDateTime(n["FilterTime"]),
                    FilterContent = n["FilterContent"] is DBNull ? string.Empty : n["FilterContent"].ToString()
                });
            }
            else
            {
                return new List<ProfileFilterCus>();
            }
        }

        public IEnumerable<CMSConsiderAndDecide> GetAllConsiderAndDecide()
        {
            var conn = new SqlConnection(Ultility.connectionString);
            conn.Open();
            var cmd = new SqlCommand();
            cmd.CommandText = "sp_GetAllConsiderAndDecide";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            var reader = cmd.ExecuteReader();

            if (reader != null && reader.HasRows)
            {
                return reader.DatareaderToList(n => new CMSConsiderAndDecide
                {
                    ConsiderAndDecideID = n["ConsiderAndDecideID"] is DBNull ? Guid.Empty : Guid.Parse(n["ConsiderAndDecideID"].ToString()),
                    ConsiderAndDecideName = n["ConsiderAndDecideName"] is DBNull ? string.Empty : n["ConsiderAndDecideName"].ToString(),
                    ProfileName = n["ProfileName"] is DBNull ? string.Empty : n["ProfileName"].ToString(),
                    CreatedDate = n["CreatedDate"] is DBNull ? DateTime.MinValue : Convert.ToDateTime(n["CreatedDate"]),
                    DiseaseName = n["DiseaseName"] is DBNull ? string.Empty : n["DiseaseName"].ToString(),
                    PhaseOfDisease = n["PhaseOfDisease"] is DBNull ? string.Empty : n["PhaseOfDisease"].ToString(),
                    IsTreat = n["IsTreat"] is DBNull ? false : Convert.ToBoolean(n["IsTreat"]),
                    IsRecover = n["IsRecover"] is DBNull ? false : Convert.ToBoolean(n["IsRecover"]),
                    AnticipateSpend = n["AnticipateSpend"] is DBNull ? string.Empty : n["AnticipateSpend"].ToString(),
                    TreatTime = n["TreatTime"] is DBNull ? string.Empty : n["TreatTime"].ToString(),
                    CommentOfDoctor = n["CommentOfDoctor"] is DBNull ? string.Empty : n["CommentOfDoctor"].ToString(),
                    IsPoor = n["IsPoor"] is DBNull ? false : Convert.ToBoolean(n["IsPoor"]),
                    IsDirectLabour = n["IsDirectLabour"] is DBNull ? false : Convert.ToBoolean(n["IsDirectLabour"]),
                    CauseIll = n["CauseIll"] is DBNull ? string.Empty : n["CauseIll"].ToString(),
                    CommentOfConsider = n["CommentOfConsider"] is DBNull ? string.Empty : n["CommentOfConsider"].ToString()
                });
            }
            else
            {
                return new List<CMSConsiderAndDecide>();
            }
        }

        public CMSConsiderAndDecide GetConsiderAndDecideByID(Guid considerAndDecideID)
        {
            var conn = new SqlConnection(Ultility.connectionString);
            conn.Open();
            var cmd = new SqlCommand();
            cmd.CommandText = "sp_GetConsiderAndDecideByID";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ConsiderAndDecideID", SqlDbType.UniqueIdentifier).Value = considerAndDecideID;
            cmd.Connection = conn;
            var reader = cmd.ExecuteReader();

            if (reader != null && reader.HasRows)
            {
                return reader.DatareaderToList(n => new CMSConsiderAndDecide
                {
                    ConsiderAndDecideID = n["ConsiderAndDecideID"] is DBNull ? Guid.Empty : Guid.Parse(n["ConsiderAndDecideID"].ToString()),
                    ConsiderAndDecideName = n["ConsiderAndDecideName"] is DBNull ? string.Empty : n["ConsiderAndDecideName"].ToString(),
                    ProfileName = n["ProfileName"] is DBNull ? string.Empty : n["ProfileName"].ToString(),
                    CreatedDate = n["CreatedDate"] is DBNull ? DateTime.MinValue : Convert.ToDateTime(n["CreatedDate"]),
                    DiseaseName = n["DiseaseName"] is DBNull ? string.Empty : n["DiseaseName"].ToString(),
                    PhaseOfDisease = n["PhaseOfDisease"] is DBNull ? string.Empty : n["PhaseOfDisease"].ToString(),
                    IsTreat = n["IsTreat"] is DBNull ? false : Convert.ToBoolean(n["IsTreat"]),
                    IsRecover = n["IsRecover"] is DBNull ? false : Convert.ToBoolean(n["IsRecover"]),
                    AnticipateSpend = n["AnticipateSpend"] is DBNull ? string.Empty : n["AnticipateSpend"].ToString(),
                    TreatTime = n["TreatTime"] is DBNull ? string.Empty : n["TreatTime"].ToString(),
                    CommentOfDoctor = n["CommentOfDoctor"] is DBNull ? string.Empty : n["CommentOfDoctor"].ToString(),
                    IsPoor = n["IsPoor"] is DBNull ? false : Convert.ToBoolean(n["IsPoor"]),
                    IsDirectLabour = n["IsDirectLabour"] is DBNull ? false : Convert.ToBoolean(n["IsDirectLabour"]),
                    CauseIll = n["CauseIll"] is DBNull ? string.Empty : n["CauseIll"].ToString(),
                    CommentOfConsider = n["CommentOfConsider"] is DBNull ? string.Empty : n["CommentOfConsider"].ToString()
                }).SingleOrDefault();
            }
            else
            {
                return new CMSConsiderAndDecide();
            }
        }

        public int AddConsiderAndDecide(Guid considerAndDecideID, string considerAndDecideName, string diseaseName, string phaseOfDisease, bool isTreat,
               bool isRecover, string anticipateSpend, string treatTime, string commentOfDoctor, bool isPoor, bool isDirectLabour, string causeIll, string imageUrl,
            string videoUrl, string commentOfConsider, string profileName, DateTime createdDate)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "sp_AddConsiderAndDecide";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ConsiderAndDecideID", SqlDbType.UniqueIdentifier).Value = considerAndDecideID;
                    cmd.Parameters.Add("@ConsiderAndDecideName", SqlDbType.NVarChar).Value = considerAndDecideName;
                    cmd.Parameters.Add("@DiseaseName", SqlDbType.NVarChar).Value = diseaseName;
                    cmd.Parameters.Add("@PhaseOfDisease", SqlDbType.NVarChar).Value = phaseOfDisease;
                    cmd.Parameters.Add("@IsTreat", SqlDbType.Bit).Value = isTreat;
                    cmd.Parameters.Add("@IsRecover", SqlDbType.Bit).Value = isRecover;
                    cmd.Parameters.Add("@AnticipateSpend", SqlDbType.NVarChar).Value = anticipateSpend;
                    cmd.Parameters.Add("@TreatTime", SqlDbType.NVarChar).Value = treatTime;
                    cmd.Parameters.Add("@CommentOfDoctor", SqlDbType.NVarChar).Value = commentOfDoctor;
                    cmd.Parameters.Add("@IsPoor", SqlDbType.Bit).Value = isPoor;
                    cmd.Parameters.Add("@IsDirectLabour", SqlDbType.Bit).Value = isDirectLabour;
                    cmd.Parameters.Add("@CauseIll", SqlDbType.NVarChar).Value = causeIll;
                    cmd.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = imageUrl;
                    cmd.Parameters.Add("@VideoUrl", SqlDbType.NVarChar).Value = videoUrl;
                    cmd.Parameters.Add("@CommentOfConsider", SqlDbType.NVarChar).Value = commentOfConsider;
                    cmd.Parameters.Add("@ProfileName", SqlDbType.NVarChar).Value = profileName;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = createdDate;
                    cmd.Connection = conn;

                    result = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }
            return result;
        }
        //
        //
        //
        public CMSConsiderAndDecide GetConsiderAndDecideByProfile(string profileName)
        {
            var conn = new SqlConnection(Ultility.connectionString);
            conn.Open();
            var cmd = new SqlCommand();
            cmd.CommandText = "sp_GetConsiderAndDecideByProfileName";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ProfileName", SqlDbType.VarChar).Value = profileName;
            cmd.Connection = conn;
            var reader = cmd.ExecuteReader();

            if (reader != null && reader.HasRows)
            {
                return reader.DatareaderToList(n => new CMSConsiderAndDecide
                {
                    ConsiderAndDecideID = n["ConsiderAndDecideID"] is DBNull ? Guid.Empty : Guid.Parse(n["ConsiderAndDecideID"].ToString()),
                    ConsiderAndDecideName = n["ConsiderAndDecideName"] is DBNull ? string.Empty : n["ConsiderAndDecideName"].ToString(),
                    ProfileName = n["ProfileName"] is DBNull ? string.Empty : n["ProfileName"].ToString(),
                    CreatedDate = n["CreatedDate"] is DBNull ? DateTime.MinValue : Convert.ToDateTime(n["CreatedDate"]),
                    DiseaseName = n["DiseaseName"] is DBNull ? string.Empty : n["DiseaseName"].ToString(),
                    PhaseOfDisease = n["PhaseOfDisease"] is DBNull ? string.Empty : n["PhaseOfDisease"].ToString(),
                    IsTreat = n["IsTreat"] is DBNull ? false : Convert.ToBoolean(n["IsTreat"]),
                    IsRecover = n["IsRecover"] is DBNull ? false : Convert.ToBoolean(n["IsRecover"]),
                    AnticipateSpend = n["AnticipateSpend"] is DBNull ? string.Empty : n["AnticipateSpend"].ToString(),
                    TreatTime = n["TreatTime"] is DBNull ? string.Empty : n["TreatTime"].ToString(),
                    CommentOfDoctor = n["CommentOfDoctor"] is DBNull ? string.Empty : n["CommentOfDoctor"].ToString(),
                    IsPoor = n["IsPoor"] is DBNull ? false : Convert.ToBoolean(n["IsPoor"]),
                    IsDirectLabour = n["IsDirectLabour"] is DBNull ? false : Convert.ToBoolean(n["IsDirectLabour"]),
                    CauseIll = n["CauseIll"] is DBNull ? string.Empty : n["CauseIll"].ToString(),
                    CommentOfConsider = n["CommentOfConsider"] is DBNull ? string.Empty : n["CommentOfConsider"].ToString()
                }).SingleOrDefault();
            }
            else
            {
                return new CMSConsiderAndDecide();
            }
        }
    }
}